package cn.com.huzo.service.view.inventory;

import cn.com.huzo.model.entities.inventory.MaterialInventoryBookHeaderEntity;
import cn.com.huzo.model.entities.inventory.MaterialInventoryBookLinesEntity;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import static org.apache.poi.ss.util.CellUtil.getCell;
import org.springframework.web.servlet.view.document.AbstractXlsView;

/**
 * 导出材料库存台账
 *
 * @author 吴旭
 */
@SuppressWarnings("unchecked")
public class MaterialInventoryBookExcelView_01 extends AbstractXlsView {

    public MaterialInventoryBookExcelView_01() {
        super();
    }

    @Override
    protected Workbook createWorkbook(Map<String, Object> model, HttpServletRequest request) {
        HSSFWorkbook workbook = null;
        try (InputStream _tmp = new FileInputStream(request.getServletContext().getRealPath("/") + "/WEB-INF/templates/05_inventory/原材料库存台账（模版）.xls")) {
            workbook = new HSSFWorkbook(_tmp);
        } catch (FileNotFoundException ex) {
        } catch (IOException ex) {
        }

        return workbook;
    }

    /**
     *
     * @param model 文件数据
     * @param workbook 基于模版创建的excel文件
     * @param request 客户端请求
     * @param response 客户端响应
     * @throws Exception
     */
    @Override
    protected void buildExcelDocument(Map<String, Object> model, Workbook workbook,
            HttpServletRequest request, HttpServletResponse response) throws Exception {
        int start_row = 12;   // 起始明细行

        // 显示对话框提示用户下载或打开
        response.setContentType("application/vnd.ms-excel");
        response.setHeader("Content-Disposition", "attachment;filename=" + new String("原材料库存台账.xls".getBytes(), "ISO8859-1"));

        Sheet sheet = workbook.getSheetAt(0);                          // 从模版取得第一个sheet
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");     // 设置日期格式

        // step 1：设置表头
        MaterialInventoryBookHeaderEntity item = (MaterialInventoryBookHeaderEntity) model.get("HeaderEntity");
        if (item != null) {
            if (item.getSerialNo() != null) {
                getCell(sheet.getRow(6), 1).setCellValue(item.getSerialNo());
            }
            if (item.getSoNo() != null) {
                getCell(sheet.getRow(6), 5).setCellValue(item.getSoNo());
            }
            if (item.getProductName() != null) {
                getCell(sheet.getRow(6), 9).setCellValue(item.getProductName());
            }
            if (item.getStyleNo() != null) {
                getCell(sheet.getRow(6), 14).setCellValue(item.getStyleNo());
            }
            if (item.getQuantity() != null) {
                getCell(sheet.getRow(6), 21).setCellValue(item.getQuantity());
            }
        }
        // step 2：设置表明细
        List<MaterialInventoryBookLinesEntity> itemList = (List<MaterialInventoryBookLinesEntity>) model.get("LinesList");
        int sequence = 1;
        for (MaterialInventoryBookLinesEntity line : itemList) {
            // 根据模版空白行创建新行（先把模版空白行到最后一行向下移动一行，在移动后的地方（移动后留的空白）新建一个新行，然后复制模版行的样式）
            sheet.shiftRows(start_row, sheet.getLastRowNum(), 3, true, false);
            for (int j = 0; j < 3; j++) {
                Row newRow = sheet.createRow(start_row + j);
                Row sourceRow = sheet.getRow(start_row + j + 3);
                newRow.setHeight(sourceRow.getHeight());

                for (int i = 0; i < sourceRow.getLastCellNum(); i++) {
                    Cell oldCell = sourceRow.getCell(i);
                    Cell newCell = newRow.createCell(i);
                    if (oldCell == null) {
                        continue;
                    }
                    newCell.setCellStyle(oldCell.getCellStyle());
                }
            }
            CellStyle _new_cell_style = workbook.createCellStyle();
            _new_cell_style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            _new_cell_style.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex());
            Font _font = workbook.createFont();
            _font.setFontName("宋体");
            _font.setBold(true);
            _font.setFontHeightInPoints((short) 11);//设置字体大小  
            _new_cell_style.setBorderBottom(BorderStyle.THIN);
            _new_cell_style.setBorderTop(BorderStyle.THIN);
            _new_cell_style.setBorderLeft(BorderStyle.THIN);
            _new_cell_style.setBorderRight(BorderStyle.THIN);
            _new_cell_style.setAlignment(HorizontalAlignment.CENTER);
            _new_cell_style.setVerticalAlignment(VerticalAlignment.CENTER);
            _new_cell_style.setFont(_font);//选择需要用到的字体格式  
            DataFormat format = workbook.createDataFormat();
            _new_cell_style.setDataFormat(format.getFormat("#,##0.00"));

            // 填充数据
            sheet.addMergedRegion(new CellRangeAddress(start_row, start_row + 2, 0, 0));
            getCell(sheet.getRow(start_row), 0).setCellValue(sequence);
            sheet.addMergedRegion(new CellRangeAddress(start_row, start_row + 2, 1, 2));
            if (line.getMaterialName() != null) {
                getCell(sheet.getRow(start_row), 1).setCellValue(line.getMaterialName());
            }
            sheet.addMergedRegion(new CellRangeAddress(start_row, start_row + 2, 3, 4));
            if (line.getMaterialNo() != null) {
                getCell(sheet.getRow(start_row), 3).setCellValue(line.getMaterialNo());
            }
            sheet.addMergedRegion(new CellRangeAddress(start_row, start_row + 2, 5, 6));
            if (line.getMaterialColourName() != null) {
                String _tmp;
                if ("".equals(line.getMaterialColourName())) {
                    _tmp = line.getSpecification();
                } else {
                    _tmp = line.getMaterialColourName() + "\n" + line.getSpecification();
                }
                getCell(sheet.getRow(start_row), 5).setCellValue(_tmp);
            }
            sheet.addMergedRegion(new CellRangeAddress(start_row, start_row + 2, 7, 8));
            if (line.getPoQuantity() != null) {
                getCell(sheet.getRow(start_row), 7).setCellValue(line.getPoQuantity());
            }

            sheet.addMergedRegion(new CellRangeAddress(start_row, start_row + 1, 9, 9));
            sheet.addMergedRegion(new CellRangeAddress(start_row, start_row + 1, 10, 10));
            sheet.addMergedRegion(new CellRangeAddress(start_row, start_row + 1, 11, 11));
            sheet.addMergedRegion(new CellRangeAddress(start_row, start_row + 1, 12, 12));
            sheet.addMergedRegion(new CellRangeAddress(start_row, start_row + 2, 13, 13));
            sheet.addMergedRegion(new CellRangeAddress(start_row, start_row + 2, 18, 18));
            sheet.addMergedRegion(new CellRangeAddress(start_row, start_row + 2, 19, 20));
            sheet.addMergedRegion(new CellRangeAddress(start_row, start_row + 2, 21, 24));

            start_row = start_row + 3;
            sequence = sequence + 1;
        }
        // 删除最后模版空白行
        //removeMergedRegion(sheet, start_row + 3, start_row +3 + 2);
        //sheet.shiftRows(start_row + 3, sheet.getLastRowNum(), -1, true, false);
        //sheet.getLastRowNum()
    }

    // 删除指定行的合并单元格
    private void removeMergedRegion(Sheet sheet, int row, int column) {
        int sheetMergeCount = sheet.getNumMergedRegions();  // 获取所有的单元格 
        int index = 0;//用于保存要移除的那个单元格序号
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress ca = sheet.getMergedRegion(i); // 获取第i个单元格
            int firstColumn = ca.getFirstColumn();
            int lastColumn = ca.getLastColumn();
            int firstRow = ca.getFirstRow();
            int lastRow = ca.getLastRow();
            if (row >= firstRow && row <= lastRow) {
                if (column >= firstColumn && column <= lastColumn) {
                    index = i;
                }
            }
        }
        sheet.removeMergedRegion(index); // 移除合并单元格
    }
}
